package com.yuanqi.dz.dc.adapters;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import pnbclient.helper.StringHelper;

import com.yuanqitech.powermop.dz.DZInfo;
import com.yuanqitech.powermop.dz.DZInfoArray;

public class ReadExcel_bak {
	private static  int  START_ROW = 3 ; 

	public ReadExcel_bak(){
		//START_ROW =  Integer.valueOf(DZConfigRead.getProperties("start_row")) ;
	}

	public List<DZInfo> readXlsx(String path) throws IOException {
		//System.out.println(Common.PROCESSING + path);
		InputStream is = new FileInputStream(path);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

		List<DZInfo> list = new ArrayList<DZInfo>();
		// Read the Sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}
			// Read the Row
			DZInfo dzInfo = null;
			//xssfSheet.getLastRowNum()
			for (int rowNum = START_ROW; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow != null) {
					dzInfo = new DZInfo();
					XSSFCell tramid = xssfRow.getCell(3);
					XSSFCell bankname = xssfRow.getCell(5);
					XSSFCell cardnostart = xssfRow.getCell(7);
					XSSFCell cardnoend = xssfRow.getCell(8);
					XSSFCell tran_date = xssfRow.getCell(9);
					XSSFCell tran_time = xssfRow.getCell(10);
					XSSFCell tran_code = xssfRow.getCell(11);
					XSSFCell refer = xssfRow.getCell(25);

					String trancode = getValue(tran_code);
					if("消费".equals(trancode)){
						trancode = "S1";
					}else if("消费撤销".equals(trancode)){
						trancode = "S2";
					}else if("联机退货".equals(trancode)){
						trancode = "S3";
					}
					XSSFCell auth_code = xssfRow.getCell(12);
					XSSFCell amount = xssfRow.getCell(13);

					dzInfo.setTermId(getValue(tramid));
					dzInfo.setBankName(getValue(bankname));
					dzInfo.setTranCardNum(getValue(cardnostart)+getValue(cardnoend));
					dzInfo.setTrDateTime(getValue(tran_date)+getValue(tran_time));
					dzInfo.setTranCode(trancode);
					dzInfo.setAuthCode(getValue(auth_code));
					dzInfo.setAmount( Float.valueOf(getValue(amount)));
					dzInfo.setReferNum(getValue(refer));
					list.add(dzInfo);
				}
			}
		}

		
		return list;
	}

	public static void main(String[] args) throws IOException {
		String path = "D:\\dz\\10210003570000_20170301_1.xls";
		List<DZInfo> list = new ReadExcel_bak().readXls(path);
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
		}
		System.out.println(list.size());
	}
	/**
	 * Read the Excel 2003-2007
	 * @param path the path of the Excel
	 * @return
	 * @throws IOException
	 */
	public List<DZInfo> readXls(String path) throws IOException {

		InputStream is = new FileInputStream(path);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

		List<DZInfo> list = new ArrayList<DZInfo>();
		// Read the Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// Read the Row
			DZInfo dzInfo = null;
			//hssfSheet.getLastRowNum()
			for (int rowNum = START_ROW; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {

					dzInfo = new DZInfo();
					HSSFCell refer = hssfRow.getCell(11);
					String refernum = getValue(refer);
					if(null == refer ||  StringHelper.isEmpty(refernum)){
						continue;
					}else{
					  Boolean isNumFlag = isNumeric(refernum);
					  if(!isNumFlag){
						  continue;
					  }
					}
					HSSFCell tramid = hssfRow.getCell(4);
					HSSFCell bankname = hssfRow.getCell(14);
					HSSFCell cardno = hssfRow.getCell(6);
					HSSFCell merchantnum = hssfRow.getCell(0);
					HSSFCell ext6 = hssfRow.getCell(13);
					String merchant_num = getValue(merchantnum);
					dzInfo.setMerchantNum(merchant_num);
					
					String trancardnum =  getValue(cardno);
					if(null!=trancardnum && trancardnum.contains("-")){
						trancardnum = trancardnum.split("-")[0];
					}
					HSSFCell tran_date = hssfRow.getCell(1);
					HSSFCell tran_time = hssfRow.getCell(3);
					HSSFCell tran_code = hssfRow.getCell(5);
					String trancode = getValue(tran_code);
					if("消费".equals(trancode)) trancode = "S1";
					else if("消费撤销".equals(trancode)) trancode = "S2";
					else if("联机退货".equals(trancode)) trancode = "S3";
					HSSFCell auth_code = hssfRow.getCell(12);
					HSSFCell amount = hssfRow.getCell(7);

					dzInfo.setTermId(getValue(tramid));
					dzInfo.setBankName(getValue(bankname));
					dzInfo.setTranCardNum(trancardnum);
					dzInfo.setTrDateTime(getValue(tran_date)+" "+getValue(tran_time));
					dzInfo.setTranCode(trancode);
					dzInfo.setExt5(getValue(auth_code)); //系统跟踪号
					dzInfo.setExt6(getValue(ext6));   //卡类型
					dzInfo.setAmount( Float.valueOf(getValue(amount)));
					dzInfo.setReferNum(getValue(refer));
					list.add(dzInfo);
				}
			}
		}

		return list;
	}
	
	
	/**
	 * Read the Excel 2003-2007
	 * @param path the path of the Excel
	 * @return
	 * @throws IOException
	 */
	public List<DZInfo> readXls(String path,String merchant_code,String is_filter) throws IOException {

		InputStream is = new FileInputStream(path);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

		List<DZInfo> list = new ArrayList<DZInfo>();
		// Read the Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// Read the Row
			DZInfo dzInfo = null;
			//hssfSheet.getLastRowNum()
			for (int rowNum = START_ROW; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {

					dzInfo = new DZInfo();
					HSSFCell refer = hssfRow.getCell(11);
					String refernum = getValue(refer);
					if(null == refer ||  StringHelper.isEmpty(refernum)){
						continue;
					}else{
					  Boolean isNumFlag = isNumeric(refernum);
					  if(!isNumFlag){
						  continue;
					  }
					}
					HSSFCell tramid = hssfRow.getCell(4);
					HSSFCell bankname = hssfRow.getCell(14);
					HSSFCell cardno = hssfRow.getCell(6);
					HSSFCell merchantnum = hssfRow.getCell(0);
					HSSFCell ext6 = hssfRow.getCell(13);
					String merchant_num = getValue(merchantnum);
					dzInfo.setMerchantNum(merchant_num);
					
					String trancardnum =  getValue(cardno);
					if(null!=trancardnum && trancardnum.contains("-")){
						trancardnum = trancardnum.split("-")[0];
					}
					HSSFCell tran_date = hssfRow.getCell(1);
					HSSFCell tran_time = hssfRow.getCell(3);
					HSSFCell tran_code = hssfRow.getCell(5);
					String trancode = getValue(tran_code);
					if("消费".equals(trancode)) trancode = "S1";
					else if("消费撤销".equals(trancode)) trancode = "S2";
					else if("联机退货".equals(trancode)) trancode = "S3";
					HSSFCell auth_code = hssfRow.getCell(12);
					HSSFCell amount = hssfRow.getCell(7);

					dzInfo.setTermId(getValue(tramid));
					dzInfo.setBankName(getValue(bankname));
					dzInfo.setTranCardNum(trancardnum);
					dzInfo.setTrDateTime(getValue(tran_date)+" "+getValue(tran_time));
					dzInfo.setTranCode(trancode);
					dzInfo.setExt5(getValue(auth_code)); //系统跟踪号
					dzInfo.setExt6(getValue(ext6));   //卡类型
					dzInfo.setAmount( Float.valueOf(getValue(amount)));
					dzInfo.setReferNum(getValue(refer));
					if(null == is_filter || !"1".equals(is_filter)){
						list.add(dzInfo);
					}else{
						if(merchant_code.equals(merchant_num)){
							list.add(dzInfo);
						}
					}
					
				}
			}
		}

		return list;
	}
	
	
	
	/**
	 * Read the Excel 2003-2007
	 * @param path the path of the Excel
	 * @return
	 * @throws IOException
	 */
	public List<DZInfo> readXls(String path,String mercharnum) throws IOException {

		InputStream is = new FileInputStream(path);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

		List<DZInfo> list = new ArrayList<DZInfo>();
		// Read the Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// Read the Row
			DZInfo dzInfo = null;
			//hssfSheet.getLastRowNum()
			for (int rowNum = START_ROW; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {
					dzInfo = new DZInfo();
					if(!"".endsWith(String.valueOf(hssfRow.getCell(1))) 
							&& !"null".endsWith(String.valueOf(hssfRow.getCell(1)))){
						HSSFCell tran_date = hssfRow.getCell(1);//交易日期
						HSSFCell tran_time = hssfRow.getCell(2);//交易时间
						HSSFCell tramid = hssfRow.getCell(3);//终端号
						HSSFCell cardno = hssfRow.getCell(4);//卡号
						HSSFCell tran_code = hssfRow.getCell(5);//交易类型
						String trancode = getValue(tran_code);
						if("消费".equals(trancode)) trancode = "S1";
						else if("消费撤销".equals(trancode)) trancode = "S2";
						else if("联机退货".equals(trancode)) trancode = "S3";
						HSSFCell amount = hssfRow.getCell(6);//交易金额
						HSSFCell payFlowNO = hssfRow.getCell(9);//流水号
						HSSFCell bankname = hssfRow.getCell(11);//发卡银行名称
						
						dzInfo.setTrDateTime(getValue(tran_date)+" "+getValue(tran_time));
						dzInfo.setTermId(getValue(tramid));
						dzInfo.setTranCardNum(getValue(cardno));
						dzInfo.setTranCode(trancode);
						dzInfo.setAmount(Float.valueOf(getValue(amount)));
						dzInfo.setReferNum(getValue(payFlowNO));
						dzInfo.setPayFlowNO(getValue(payFlowNO));
						dzInfo.setBankName(getValue(bankname));
						dzInfo.setMerchantNum(mercharnum);
						list.add(dzInfo);
					}else{
						break;
					}
				}
			}
		}
		return list;
	}
	
    
	public DZInfoArray getDataFromXls(String path) throws IOException{
		List<DZInfo> list =  readXls(path);
		DZInfoArray dzInfoArray = new DZInfoArray();
		if(null == list || list.size()==0){
			dzInfoArray = new DZInfoArray();
			dzInfoArray.setRc("-1");
			dzInfoArray.setDzInfo(new DZInfo[0]);
			dzInfoArray.setErrormsg("获取对账数据为null");
			return dzInfoArray;
		}
		dzInfoArray = new DZInfoArray();
		dzInfoArray.setRc("1");
		dzInfoArray.setDzInfo(list.toArray(new DZInfo[list.size()]));
		return dzInfoArray;
	}
	
	public DZInfoArray getDataFromXls(String path,String mercharnum) throws IOException{
		List<DZInfo> list =  readXls(path, mercharnum);
		DZInfoArray dzInfoArray = new DZInfoArray();
		if(null == list || list.size()==0){
			dzInfoArray = new DZInfoArray();
			dzInfoArray.setRc("-1");
			dzInfoArray.setDzInfo(new DZInfo[0]);
			dzInfoArray.setErrormsg("获取对账数据为null");
			return dzInfoArray;
		}
		dzInfoArray = new DZInfoArray();
		dzInfoArray.setRc("1");
		dzInfoArray.setDzInfo(list.toArray(new DZInfo[list.size()]));
		return dzInfoArray;
	}
	
	public DZInfoArray getDataFromXls(String path,String merchant_num,String is_filter) throws IOException{
		List<DZInfo> list =  readXls(path,merchant_num,is_filter);
		DZInfoArray dzInfoArray = new DZInfoArray();
		if(null == list || list.size()==0){
			dzInfoArray = new DZInfoArray();
			dzInfoArray.setRc("-1");
			dzInfoArray.setDzInfo(new DZInfo[0]);
			dzInfoArray.setErrormsg("获取对账数据为null");
			return dzInfoArray;
		}
		dzInfoArray = new DZInfoArray();
		dzInfoArray.setRc("1");
		dzInfoArray.setDzInfo(list.toArray(new DZInfo[list.size()]));
		return dzInfoArray;
	}
	
	@SuppressWarnings("static-access")
	private String getValue(XSSFCell xssfRow) {
		if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfRow.getBooleanCellValue());
		} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfRow.getNumericCellValue());
		} else {
			return String.valueOf(xssfRow.getStringCellValue());
		}
	}

	@SuppressWarnings("static-access")
	private String getValue(HSSFCell hssfCell) {
		if(null == hssfCell){
			return "";
		}
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}


	public static boolean isNumeric(String str){
		for (int i = 0; i < str.length(); i++){
			
			if (!Character.isDigit(str.charAt(i))){
				return false;
			}
		}
		return true;
	}
}